package com.cx.mall.demo.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcText {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Test

    public void select01() throws Exception {
        try {
            // 1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            String url = "jdbc:mysql://192.168.100.180:3306/mall_user?characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);
            // 3. 定义sql
            String sql = "select * from user_info where username = ? and password = ?";// ?:表示一个参数,也称占位符
            //4. 获取执行sql的对象 Statement
            ps = conn.prepareStatement(sql);
            ps.setObject(1, "test");// 给第一个参数(?)赋值
            ps.setObject(2, "123456");// 给第二个参数(?)赋值
            //5. 执行sql
            rs = ps.executeQuery();//返回查询的结果集
            //6. 处理结果
            while (rs.next()) { //如果有数据，rs.next()返回true
                System.out.println("id:" + rs.getString("id"));
                System.out.println("username:" + rs.getString("username"));
                System.out.println("nick_name:" + rs.getString("nick_name"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //7. 释放资源
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Test
    public void insert02() throws Exception {
        try {
            // 1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            String url = "jdbc:mysql://192.168.100.180:3306/mall_user?characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, username, password);
            // 3. 定义sql
            String sql = "INSERT INTO `user_info` VALUES (?,?,?,?,?,?,?,?)";
            //4. 获取执行sql的对象 Statement
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, 15);
            ps.setObject(2, "orderAdmin");
            ps.setObject(3, "123456");
            ps.setObject(4, "order@qq.com");
            ps.setObject(5, "订单管理员");
            ps.setObject(6, "只有订单权限");
            ps.setObject(7, "2025-02-18 08:15:08");
            ps.setObject(8, 0);
            //5. 执行sql
            ps.execute();
            //6. 释放资源

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    @Test
    public void select05() throws Exception {
        // 1. 注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获取连接
            // 2.1 定义配置文件
            // 3. 定义sql
            conn =getConnection();
            String sql = "select * from user_info where username = ? and password = ?";// ?:表示一个参数,也称占位符
            //4. 获取执行sql的对象 Statement
            ps = conn.prepareStatement(sql);
            ps.setObject(1, "test");// 给第一个参数(?)赋值
            ps.setObject(2, "123456");// 给第二个参数(?)赋值
            //5. 执行sql
            rs = ps.executeQuery();//返回查询的结果集
            //6. 处理结果
            while (rs.next()) { //如果有数据，rs.next()返回true
                System.out.println("id:" + rs.getString("id"));
                System.out.println("username:" + rs.getString("username"));
                System.out.println("nick_name:" + rs.getString("nick_name"));
            }
            //7. 释放资源

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    public Connection getConnection() throws Exception {
        Properties properties = new Properties();
        // 2.2 加载配置文件
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(inputStream);
        //2.3 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        // 2.4 获取数据库连接 Connection
        return dataSource.getConnection();
    }
}